<?php
/** Error reporting */
	error_reporting(E_ALL);
	ini_set('display_errors', TRUE);
	ini_set('display_startup_errors', TRUE);
	date_default_timezone_set('Europe/London');

	if (PHP_SAPI == 'cli')
		die('This example should only be run from a Web Browser');

	/** Include PHPExcel */
	require_once 'protected/extensions/PHPExcel.php';

	// Create new PHPExcel object
	//$objPHPExcel = new PHPExcel();

	// Load file
	$objPHPExcel = PHPExcel_IOFactory::load('protected/views/site/pages/DS_lop_bi_huy.xlsx');

	// Khởi tạo STT và Lastrow
	$stt=1;
	$lastrow=10;
	$namhoc=(string)Yii::app()->request->cookies['year'];
	//$namhoc=2013;
	
	// Truy cập CSDL, lấy thông tin, ghi vào file
		//Lấy tất cả danh sách nhóm lớp bị hủy
		
		$connection=Yii::app()->db;
		$command=$connection->createCommand("SELECT * FROM `phancong` WHERE ((`magvchinh`='') AND (`magvphu`='') AND (`phancong`.`ghichu`='".$namhoc."')) ORDER BY SUBSTRING_INDEX( SUBSTRING_INDEX( `maNhom`,  '_' , -2 ) ,  '_', 1 ) ;")->query();
		foreach ($command as $row)
		{			
			// Tìm Tên môn học và thông tin nhóm
			$tenmh;
			$hocky;
			$sosv;
			$khoilop;
			$command2=$connection->createCommand("SELECT * FROM `monhoc` WHERE `mamh`='".substr($row['maNhom'],0,6)."';")->query();
			//echo "<script>alert('".substr($row['maNhom'],1,6)."');</script>";
			foreach($command2 as $row2)
			{
				$tenmh=$row2['tenmh'];
				break;
			}
			$command3=$connection->createCommand("SELECT * FROM `nhomlop` WHERE `maNhom`='".$row['maNhom']."';")->query();
			foreach($command3 as $row3)
			{
				$hocky=$row3['hocki'];
				$sosv=$row3['soSV'];
				$khoilop=$row3['makhoilop'];
				break;
			}
			
			// Thêm 1 dòng và ghi dữ liệu
			$objPHPExcel->getActiveSheet()->insertNewRowBefore($lastrow,1);
			$objPHPExcel->setActiveSheetIndex(0)
				->setCellValue('A'.$lastrow, $stt)
				->setCellValue('B'.$lastrow, $row['maNhom'])
				->setCellValue('C'.$lastrow, $tenmh)
				->setCellValue('D'.$lastrow, $hocky)
				->setCellValue('E'.$lastrow, $sosv)
				->setCellValue('F'.$lastrow, $khoilop);
			$lastrow=$lastrow+1;
			$stt=$stt+1;
		}
	
	
	// Rename worksheet
	//$objPHPExcel->getActiveSheet()->setTitle('Simple');

	// Set active sheet index to the first sheet, so Excel opens this as the first sheet
	//$objPHPExcel->setActiveSheetIndex(0);

	// save file
	//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	//$objWriter->save('test.xls');

	// Redirect output to a client’s web browser (Excel2007)
	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header('Content-Disposition: attachment;filename="ThongkeNhomBihuy.xlsx"');
	header('Cache-Control: max-age=0');

	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter->save('php://output');
	die;exit;
?>